package com.mp.approvalreq.dao.impl;

import com.mp.approvalreq.dao.FlowDao;
import com.mp.approvalreq.entity.Flow;
import com.plusmoney.util.DateTimeHelper;
import com.plusmoney.util.SQLLogger;
import com.plusmoney.util.Utils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;

public class FlowDaoImpl extends AbstractDao implements FlowDao {

    private static final Logger logger = LoggerFactory.getLogger(FlowDaoImpl.class);

    private Connection connection;

    public FlowDaoImpl(Connection connection) {
        this.connection = connection;
    }

    private PreparedStatement getPreparedStatement(String sql) throws SQLException {
        return connection.prepareStatement(sql);
    }

    @Override
    String getQuerySql(String condition) {
        final String query = "select id, request_id, company_id, company_info_id, contact_id,"
                + " ordering, type, is_approved, is_approved_at, flow_type,"
                + " state"
                + " from sys_approval_flow";
        return String.format("%s %s", query, condition);
    }

    @Override
    Flow getObject(ResultSet rs) throws SQLException {
        Flow flow = new Flow();
        flow.setId(rs.getInt(1));
        flow.setRequestId(rs.getInt(2));
        flow.setCompanyId(rs.getInt(3));
        flow.setCompanyInfoId(rs.getInt(4));
        flow.setContactId(rs.getInt(5));

        flow.setOrdering(rs.getInt(6));
        flow.setType(rs.getInt(7));
        flow.setIsApproved(rs.getInt(8));
        flow.setIsApprovedAt(DateTimeHelper.format(rs.getTimestamp(9), DateTimeHelper.DEFAULT_PATTERN));
        flow.setFlowType(rs.getInt(10));

        flow.setState(rs.getInt(11));

        return flow;
    }


    @Override
    public void insertFlows(List<Flow> flows) throws SQLException {
        String sql = "insert into sys_approval_flow set"
                + " request_id = ?, company_id = ?, company_info_id = ?, contact_id = ?, ordering = ?,"
                + " type = ?, is_approved = ?, is_approved_at = ?, flow_type = ?, create_time = now()";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Flow flow : flows) {
                ps.setInt(1, flow.getRequestId());
                ps.setInt(2, flow.getCompanyId());
                ps.setInt(3, flow.getCompanyInfoId());
                ps.setInt(4, flow.getContactId());
                ps.setInt(5, flow.getOrdering());
                ps.setInt(6, flow.getType());
                ps.setInt(7, flow.getIsApproved());
                ps.setString(8, flow.getIsApprovedAt());
                ps.setInt(9, flow.getFlowType());

                logger.info(SQLLogger.logSQL(sql, flow.getRequestId(), flow.getCompanyId(), flow.getCompanyInfoId(), flow.getContactId(),
                        flow.getOrdering(), flow.getType(), flow.getIsApproved(), flow.getIsApprovedAt(), flow.getFlowType()));
                ps.addBatch();
            }
            ps.executeBatch();
        }
    }

    @Override
    public List<Flow> selectFlowsByRequestId(Integer requestId) throws SQLException {
        List<Flow> list = new ArrayList<>();
        String sql = getQuerySql("where request_id = ? and state < 3 order by ordering");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public void updateFlowState(Integer requestId, Integer companyId) throws SQLException {
        String sql = "update sys_approval_flow set state = 2, update_time = now() where request_id = ? and company_id = ? and state < 3";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            ps.setInt(2, companyId);
            logger.info(SQLLogger.logSQL(sql, requestId, companyId));

            ps.executeUpdate();
        }
    }

    @Override
    public List<Integer> selectNeedUserApproveIds(Integer companyId, Integer companyInfoId) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = "select request_id"
                + " from sys_approval_flow"
                + " force index(index_company_info_id)"
                + " where company_id = ? and company_info_id = ? and is_approved = 0 and state = 1 order by request_id desc";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public List<Integer> selectNeedUserApproveIdsForRedPoint(Integer companyId, Integer companyInfoId) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = "select request_id"
                + " from sys_approval_flow"
                + " force index(index_company_info_id)"
                + " where company_id = ? and company_info_id = ? and is_approved = 0 and state = 1";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public List<Integer> selectRequestIdsApprovedByCompanyInfoId(Integer companyId, Integer companyInfoId, String orderBy) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = "select distinct(flow.request_id)"
                + " from sys_approval_flow flow"
                + " left join request_flow f on flow.request_id = f.Frequest_id"
                + " where flow.company_id = ? and flow.company_info_id = ? and flow.is_approved > 0"
                + " and f.Ffinished not in(-1,-99) and flow.state < 3 and f.Fis_resubmit = 0";

        // 排序
        if (StringUtils.isEmpty(orderBy) || "0".equals(orderBy) || "1".equals(orderBy)) {
            sql += " order by f.Frequest_id desc";
        } else if ("2".equals(orderBy)) {
            sql += " order by f.Flatest_approved_time desc";
        } else if ("3".equals(orderBy)) {
            sql += " order by f.Ffinally_cc_time desc";
        }

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public List<Integer> selectRequestIdsApprovedByCompanyInfoIdWithRedPoint(Integer companyId, Integer companyInfoId, String redRequestIds) throws SQLException {
        List<Integer> list = new ArrayList<>();
        if (StringUtils.isEmpty(redRequestIds)) {
            return list;
        }
        String sql = "select distinct(flow.request_id)"
                + " from sys_approval_flow flow"
                + " inner join (select request_id from sys_approval_flow where request_id in(" + redRequestIds + ")) flow1 on flow.request_id = flow1.request_id"
                + " left join request_flow f on flow.request_id = f.Frequest_id"
                + " where flow.company_id = ? and flow.company_info_id = ? and flow.is_approved > 0"
                + " and f.Ffinished > -99 and flow.state < 3 and f.Fis_resubmit = 0";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public void updateBatchFlow(List<Flow> flows) throws SQLException {
        String sql = "update sys_approval_flow set is_approved = ?, is_approved_at = ?, update_time = now()"
                + " where company_id = ? and id = ? limit 1";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Flow flow : flows) {
                ps.setInt(1, flow.getIsApproved());
                ps.setString(2, flow.getIsApprovedAt());
                ps.setInt(3, flow.getCompanyId());
                ps.setInt(4, flow.getId());
                logger.info(SQLLogger.logSQL(sql, flow.getIsApproved(), flow.getIsApprovedAt(), flow.getCompanyId(), flow.getId()));

                ps.addBatch();
            }

            ps.executeBatch();
        }

    }

    @Override
    public void deleteBatchFlows(List<Flow> flows) throws SQLException {
        String sql = "update sys_approval_flow set state = 3, update_time = now()"
                + " where company_id = ? and id = ? limit 1";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Flow flow : flows) {
                ps.setInt(1, flow.getCompanyId());
                ps.setInt(2, flow.getId());
                logger.info(SQLLogger.logSQL(sql, flow.getCompanyId(), flow.getId()));

                ps.addBatch();
            }

            ps.executeBatch();
        }

    }

    @Override
    public Map<Integer, List<Flow>> selectFlowMapByRequestIds(String requestIds) throws SQLException {
        Map<Integer, List<Flow>> map = new HashMap<>();
        if (StringUtils.isBlank(requestIds)) {
            return map;
        }
        String sql = getQuerySql("where request_id in(" + requestIds + ") and state = 1 order by request_id, ordering");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            logger.info(sql);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Flow flow = getObject(rs);
                    if (!map.containsKey(flow.getRequestId())) {
                        map.put(flow.getRequestId(), new ArrayList<>());
                    }
                    map.get(flow.getRequestId()).add(flow);
                }
            }
        }
        return map;
    }

    @Override
    public void deleteBatchFlowsByRequestIds(Integer companyId, String requestIds) throws SQLException {
        if (StringUtils.isEmpty(requestIds)) {
            return;
        }
        String sql = "update sys_approval_flow set state = 3 where company_id = ? and request_id in(" + requestIds + ")";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            ps.executeUpdate();
        }
    }

}
